﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using Common;
using Models;
namespace DAL
{
    public class AccountDAL
    {
        //列出所有
        public DataTable ListAll()
        {
           
            DataTable dt = SqlHelper.ExecuteDataTable("select * from account where  isdel=@isdel  order by edittime desc",
                new MySqlParameter("@isdel", "0"));
            return dt;
        }
        //列出所有大唐账户
        public DataTable ListDotonAll() {
            return SqlHelper.ExecuteDataTable("select * from account where  isdel=@isdel and company=0  order by edittime desc", new MySqlParameter("@isdel", "0"));
        }

        //列出所有大唐账户
        public DataTable ListOemAll()
        {
            return SqlHelper.ExecuteDataTable("select * from account where  isdel=@isdel and company=1  order by edittime desc", new MySqlParameter("@isdel", "0"));
        }

        //列出所有
        public DataTable ListAll(Dictionary<string,string> dic) {
            string sql = string.Empty;
            if (!string.IsNullOrWhiteSpace(dic["acname"])) { 
                sql+=" and acname like '%"+dic["acname"]+"%'";
            }
            if (!string.IsNullOrWhiteSpace(dic["acbank"]))
            {
                sql += " and acbank like '%" + dic["acbank"] + "%'";
            }
            if (!string.IsNullOrWhiteSpace(dic["account"]))
            {
                sql += " and accountnum like '%" + dic["account"] + "%'";
            }
            DataTable dt = SqlHelper.ExecuteDataTable("select * from account where  isdel=@isdel "+sql+" order by edittime desc",
                new MySqlParameter("@isdel", "0"));
            return dt;
        }
        //根据主键删除
        public int DeleData( int aid) {
            //deleter from account where aid =@aid
            int i = SqlHelper.ExecuteNonQuery("update account set isdel=1,editTime=@editTime,editUser=@editUser where aid =@aid ",
                new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@editUser", RemeberName.Username), new MySqlParameter("@aid", aid));
            return i;
        }
        //保存数据
        public int SaveData(Account ac ) {
          
            //StringBuilder sb = new StringBuilder();
            //sb.Append("insert into account(acname,acbank,account) values");
            //foreach(DataRow dr in dt.Rows){
            //    sb.Append("('" + SqlHelper.FromDbValue(dr["acname"]) + "','" + SqlHelper.FromDbValue(dr["acbank"]) + "','" + SqlHelper.FromDbValue(dr["account"]) + "'),");
            //   // DataRow dr = dt.Rows[i];
            //}
            //string sql =sb.ToString().TrimEnd(',');
            return SqlHelper.ExecuteNonQuery("insert into account(acname,acbank,AccountNum,addTime,editTime,addUser,editUser,company) values (@acname,@acbank,@AccountNum,@addTime,@editTime,@addUser,@editUser,@company)",
                new MySqlParameter("@acname", ac.Acname), new MySqlParameter("@acbank", ac.Acbank), new MySqlParameter("@AccountNum", ac.AccountNum), new MySqlParameter("@addTime", DateTime.Now.ToString()), 
                new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@addUser", RemeberName.Username), new MySqlParameter("@editUser", RemeberName.Username),new MySqlParameter("@company",ac.Company));
        }
        //更新数据
        public int UpdateData(Account ac)
        {
            return SqlHelper.ExecuteNonQuery("update account set acname =@acname,acbank=@acbank ,AccountNum= @AccountNum ,editTime=@editTime,editUser=@editUser,company=@company where aid =@aid",
               new MySqlParameter("@acname", ac.Acname), new MySqlParameter("@acbank", ac.Acbank), new MySqlParameter("@AccountNum", ac.AccountNum),
               new MySqlParameter("@editTime", DateTime.Now.ToString()), new MySqlParameter("@editUser", RemeberName.Username),new MySqlParameter("@company",ac.Company),new MySqlParameter("@aid", ac.Aid));
           
        }
        //DataRow转换成Account
        public Account ToAccount(DataRow dr) {
            Account ac = new Account();
            ac.Aid = Convert.ToInt32(dr["aid"]);
            ac.Acbank = dr["acbank"].ToString();
            ac.Acname = dr["acname"].ToString();
            ac.AccountNum = dr["AccountNum"].ToString();
            ac.Isdel = Convert.ToInt32(dr["isdel"]);
            ac.Company = Convert.ToInt32(dr["company"]);
            return ac;
        }
        //根据id查询
        public Account GetById(string aid) {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from account where aid = @aid",
                new MySqlParameter("aid",aid));
            if (dt.Rows.Count == 1) {
                return ToAccount(dt.Rows[0]);
            }
            else if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else {
                throw new Exception("存在重复值");
            }
        }
    }
}
